package top.yiciyuan.web.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import cn.itcast.commons.CommonUtils;

import top.yiciyuan.web.domain.Data;

public class SuperDaoImpl implements SupDao {

	/**
	 * 让用户搜索、学校所匹配的学生信息 性别 大学 学院 入学年 J值 F 昆明理工大学 管理与经济学院 2018 8
	 */
	@Override
	public List<Data> serarchStudent(String university) {
		String sql = "select * from data where university like ? limit 0,200";
		Connection connection = DBManager.getConnection();
		List<Data> list = new LinkedList<Data>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, "%"+university+"%");
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String male = resultSet.getString(1);
				String university2 = resultSet.getString(2);
				String college = resultSet.getString(3);
				String year = resultSet.getString(4);
				int j = resultSet.getInt(5);
				Data data = new Data(male, university2,college, year, j);
				list.add(data);
			}
			System.out.println(list);
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.close(connection);
		}
		return null;
	}

	/**
	 * 2 按照学校所在城市来统计学生人数两个维度 来呈现分析结果。
	 * ['2012','2013','2014','2015','2016','2017','2018 '] [11, 21, 15, 13, 12,
	 * 13, 10]
	 */
	@Override
	public List<List> showStudentNumber(String university) {
		String sql = "SELECT year,COUNT(*) FROM `data` WHERE university=?  GROUP BY year WITH ROLLUP limit 0,200";
		Connection connection = DBManager.getConnection();
		List<Integer> list1 = new LinkedList<Integer>();
		List<Integer> list2 = new LinkedList<Integer>();
		List<List> resultlist = new LinkedList<List>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, university);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String str1 = resultSet.getString(1);
				String str2 = resultSet.getString(2);
				if (str1 != null && str2 != null) {

					if (!str1.isEmpty() && !str2.isEmpty()) {
						Integer year = Integer.parseInt(str1);
						Integer number = Integer.parseInt(str2);
						list1.add(year);
						list2.add(number);
					}
				}
			}
			resultlist.add(list1);
			resultlist.add(list2);
			System.out.println("查询地图map数据完成<<<<<<<<<<<<<<<<"+new Date());
			return resultlist;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.close(connection);
		}
		return null;
	}

	/**
	 * 以地图分布的形式呈现学生数目分布。查出每个学校的学生数 [ {name: '海门', value: 9}, {name: '鄂尔多斯',
	 * value: 12} ]
	 */
	@Override
	public List<Map> getStudentMap() {
		String sql = " SELECT partment,count(*) FROM `data`,`testcollege` WHERE `data`.university=`testcollege`.university GROUP BY `testcollege`.partment;";
		Connection connection = DBManager.getConnection();
		List<Map> resultlist = new LinkedList<Map>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String str1 = resultSet.getString(1);
				String str2 = resultSet.getString(2);
				Map<String, String> map = new HashMap<String, String>();
				map.put("name", str1);
				map.put("value", str1);
				resultlist.add(map);
			}	
			return resultlist;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBManager.close(connection);
		}
		return null;
	}

}
